<?php

function get_inactive_runners_from_table($con, $table, $account_id) {
  $query = "SELECT $table.Runner FROM $table
            WHERE $table.Account = '$account_id'
            AND NOT EXISTS (
              SELECT runner.id FROM runner
              WHERE runner.id = $table.Runner);";
  $r = $con->query($query);
  if(!$r) { die("could not get top not chosen\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[] = $row[0];
  }
  return $ret;
}

function get_top_not_chosen($con, $account_id) { 
  $query = "SELECT top.Runner
            FROM top
            WHERE top.Account = '$account_id' 
            AND NOT EXISTS (
              SELECT chosen.Runner
              FROM chosen 
              WHERE chosen.Account = '$account_id' 
              AND chosen.Runner = top.Runner);";
  $r = $con->query($query);
  if(!$r) { die("could not get top not chosen\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[] = $row[0];
  }
  return $ret;
}

function get_top_double($con, $account_id) {
  $query = "SELECT Runner
            FROM top
            WHERE Account = '$account_id'
            GROUP BY Runner
            HAVING ( COUNT(Runner) > 1 );";
  $r = $con->query($query);
  if(!$r) { die("could not get top double\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[] = $row[0];
  }
  return $ret;
}

function get_chosen_double($con, $account_id) {
  $query = "SELECT Runner
            FROM chosen 
            WHERE Account = '$account_id'
            GROUP BY Runner
            HAVING ( COUNT(Runner) > 1 );";
  $r = $con->query($query);
  if(!$r) { die("could not get chosen double\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[] = $row[0];
  }
  return $ret;
}

function count_chosen($con, $account_id) {
  $query = "SELECT COUNT(Account) FROM chosen
            WHERE Account='$account_id';";
  $r = $con->query($query);
  if(!$r) { die("could not count chosen\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function count_climber($con, $account_id) {
  $query = "SELECT COUNT(Account) FROM climber 
            WHERE Account='$account_id';";
  $r = $con->query($query);
  if(!$r) { die("could not count climber\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function count_sprinter($con, $account_id) {
  $query = "SELECT COUNT(Account) FROM sprinter 
            WHERE Account='$account_id';";
  $r = $con->query($query);
  if(!$r) { die("could not count sprinter\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function count_top($con, $account_id) {
  $query = "SELECT COUNT(Account) FROM top 
            WHERE Account='$account_id';";
  $r = $con->query($query);
  if(!$r) { die("could not count sprinter\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function get_runners($con){
  $r = $con->query("SELECT id, name FROM runner;");
  if(!$r) { die("could not get runners\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $id = $row[0];
    $name = $row[1];
    $ret[$id] = $name;
  }
  return $ret;
}

function get_runners_rev($con) {
  $r = $con->query("SELECT id, name FROM runner;");
  if(!$r) { die("could not get runners reversed\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $id = $row[0];
    $name = $row[1];
    $ret[strtolower($name)] = $id;
  }
 return $ret;
}

function get_chosen($con){
  $id = sget('id');
  $query = "SELECT runner.name FROM account, chosen, runner
            WHERE account.id = '$id' AND 
            chosen.Account = account.id AND
            chosen.Runner = runner.id
            ORDER BY runner.name ASC;";
  $r = $con->query($query);
  if(!$r) { die("could not get chosen runners\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[] = $row[0];
  }
  return $ret;
}

function get_chosen_rev($con) {
  $account_id = sget('id');
  $query = "SELECT runner.id, runner.name FROM chosen, runner
            WHERE chosen.Account = '$account_id'
            AND chosen.Runner = runner.id;";
  $r = $con->query($query);
  if(!$r) { die("could not get chosen runners reversed\n" . mysql_error()); }
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $ret[$row[0]] = $row[1];
  }
  return $ret;
}

function get_sprinter($con){
  $id = sget('id');
  $query = "SELECT runner.name FROM account, sprinter, runner
            WHERE account.id = '$id' AND 
            sprinter.Account = account.id AND
            sprinter.Runner = runner.id";
  $r = $con->query($query);
  if(!$r) { die("could not get sprinter\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function get_climber($con){
  $id = sget('id');
  $query = "SELECT runner.name FROM account, climber, runner
            WHERE account.id='$id' AND 
            account.id=climber.account AND
            climber.runner=runner.id";
  $r = $con->query($query);
  if(!$r) { die("could not get climber\n" . mysql_error()); }
  $row = mysql_fetch_row($r);
  return $row[0];
}

function get_top($con){
  $id = sget('id');
  $query = "SELECT runner.name, top.number FROM account, top, runner
            WHERE account.id='$id' AND 
            account.id=top.account AND
            top.runner=runner.id
            ORDER BY top.number";
  $r = $con->query($query);
  $ret = Array();
  while($row = mysql_fetch_row($r)){
    $name = $row[0];
    $number = $row[1];
    $ret[$number] = $name;
  }
  return $ret;
}

// set

function set_climber($con, $id) {
  $account_id = sget('id');
  $query = "INSERT INTO climber (Account, Runner) VALUES ('$account_id', '$id');";
  return $con->query($query);
}

function set_sprinter($con, $id) {
  $account_id = sget('id');
  $query = "INSERT INTO sprinter (Account, Runner) VALUES ('$account_id', '$id');";
  return $con->query($query);
}

function set_top($con, $id, $number) {
  $account_id = sget('id');
  $query = "INSERT INTO top (Account, Runner, number) VALUES ('$account_id', '$id', '$number');";
  return $con->query($query);
}

function set_chosen($con, $id) {
  $account_id = sget('id');
  $query = "INSERT INTO chosen (Account, Runner) VALUES ('$account_id', '$id');";
  return $con->query($query);
}

function unset_climber($con) {
  $account_id = sget('id');
  $query = "DELETE FROM climber WHERE Account = '$account_id';";
  return $con->query($query);
}

function unset_sprinter($con) {
  $account_id = sget('id');
  $query = "DELETE FROM sprinter WHERE Account = '$account_id';";
  return $con->query($query);
}

function unset_top($con) {
  $account_id = sget('id');
  $query = "DELETE FROM top WHERE Account = '$account_id';";
  return $con->query($query);
}

function unset_chosen($con) {
  $account_id = sget('id');
  $query = "DELETE FROM chosen WHERE Account = '$account_id';";
  return $con->query($query);
}

?>
